﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Entidades.Reclamos;
using System.Data;
using Datos.Reclamos.Common;
using Datos.Reclamos.DAL;

namespace Datos.Reclamos
{
    public class RecReclamoDAL : ConexionBD, IRecReclamoDAL
    {
        #region ATRIBUTOS       
        
        private SqlCommand cmd;

        #endregion

        #region METODOS PARA LOS PRODUCTOS / SERVICIOS OFRECIDOS

        public List<GenLocal> listarLocal()
        {
            List<GenLocal> lista = new List<GenLocal>();
            GenLocal be;

            cmd = new SqlCommand();
            cmd.Connection = getConnection();
            cmd.CommandText = "sp_Rec_ListarLocal";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();

            try
            {
                cmd.Connection.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        be = new GenLocal();
                        be.IdLocal = dr.GetInt32(dr.GetOrdinal("idLocal"));
                        be.Nombre = dr.GetString(dr.GetOrdinal("nombre"));
                        lista.Add(be);
                    }
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
            return lista;
        }

        public List<RecTipoBienContratado> listarBienes()
        {
            List<RecTipoBienContratado> lista = new List<RecTipoBienContratado>();
            RecTipoBienContratado be;

            cmd = new SqlCommand();
            cmd.Connection = getConnection();
            cmd.CommandText = "sp_Rec_ListarBienes";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();

            try
            {
                cmd.Connection.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        be = new RecTipoBienContratado();
                        be.IdTipoBien = dr.GetInt32(dr.GetOrdinal("idTipoBien"));
                        be.Descripcion = dr.GetString(dr.GetOrdinal("descripcion"));
                        lista.Add(be);
                    }
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
            return lista;
        }

        #endregion

        #region METODOS PARA LOS RECLAMOS

        public int insertar(RecReclamo reclamo)
        {
            
            int codInsertado = 0;

            cmd = new SqlCommand();
            cmd.Connection = getConnection();
            cmd.CommandText = "sp_Rec_InsertarReclamo";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();

            cmd.Parameters.Add("@pIdLocal", SqlDbType.Int, 11).Value = reclamo.Local.IdLocal;
            cmd.Parameters.Add("@pNumDNI", SqlDbType.VarChar, 11).Value = reclamo.NumDNI;            
            cmd.Parameters.Add("@pNombres", SqlDbType.VarChar,1000).Value = reclamo.Nombres;
            cmd.Parameters.Add("@pFecReclamo", SqlDbType.DateTime).Value = reclamo.FecReclamo;
            cmd.Parameters.Add("@pDescripcion", SqlDbType.VarChar, 2000).Value = reclamo.Descripcion;
            cmd.Parameters.Add("@pTipo", SqlDbType.Int).Value = reclamo.Tipo;
            cmd.Parameters.Add("@pNomProducto", SqlDbType.VarChar, 1000).Value = reclamo.NomProducto;
            try
            {
                cmd.Connection.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        codInsertado = Convert.ToInt32(dr["resultado"]);
                    }
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
               cmd.Connection.Close();
               cmd.Connection.Dispose();
               cmd.Dispose();              
            }
            return codInsertado;
        }

        public int actualizar(RecReclamo reclamo)
        {
            int codInsertado = 0;
            cmd = new SqlCommand();
            cmd.Connection = getConnection();
            cmd.CommandText = "sp_Rec_ActualizarReclamo";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();

            cmd.Parameters.Add("@pIdReclamo", SqlDbType.Int, 11).Value = reclamo.IdReclamo;
            cmd.Parameters.Add("@pIdLocal", SqlDbType.Int, 11).Value = reclamo.Local.IdLocal;
            cmd.Parameters.Add("@pNumDNI", SqlDbType.VarChar, 11).Value = reclamo.NumDNI;
            cmd.Parameters.Add("@pNombres", SqlDbType.VarChar, 1000).Value = reclamo.Nombres;
            cmd.Parameters.Add("@pFecReclamo", SqlDbType.DateTime).Value = reclamo.FecReclamo;
            cmd.Parameters.Add("@pDescripcion", SqlDbType.VarChar, 2000).Value = reclamo.Descripcion;
            cmd.Parameters.Add("@pTipo", SqlDbType.Int).Value = reclamo.Tipo;
            cmd.Parameters.Add("@pNomProducto", SqlDbType.VarChar, 1000).Value = reclamo.NomProducto;
 
            try
            {
                cmd.Connection.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        codInsertado = Convert.ToInt32(dr["resultado"]);
                    }
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
                cmd.Dispose(); 
            }
            return codInsertado;
        }

        public RecReclamo obtener(int idReclamo)
        {
            RecReclamo be = new RecReclamo();
            cmd = new SqlCommand();
            cmd.Connection = getConnection();
            cmd.CommandText = "sp_Rec_ObtenerReclamo";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();

            cmd.Parameters.Add("@pIdReclamo", SqlDbType.Int, 11).Value = idReclamo;

            try
            {
                cmd.Connection.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        be = new RecReclamo();
                        be.IdReclamo = idReclamo;
                        be.Local.IdLocal = dr.GetInt32(dr.GetOrdinal("idLocal"));                        
                        be.NumDNI = dr.GetString(dr.GetOrdinal("numDNI"));
                        be.Nombres = dr.GetString(dr.GetOrdinal("nombres"));
                        be.FecReclamo = dr.GetDateTime(dr.GetOrdinal("fecReclamo"));
                        be.Descripcion = dr.GetString(dr.GetOrdinal("descripcion"));
                        be.Tipo = dr.GetInt32(dr.GetOrdinal("tipo"));
                        be.NomProducto = dr.GetString(dr.GetOrdinal("nomProducto"));  
                    }
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
                cmd.Dispose(); 
            }
            return be;
        }

        public List<RecReclamo> listar(RecReclamo reclamo)
        {
            List<RecReclamo> lista = new List<RecReclamo>();
            RecReclamo be;
            cmd = new SqlCommand();
            cmd.Connection = getConnection();
            cmd.CommandText = "sp_Rec_ListarReclamo";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();

            cmd.Parameters.Add("@pFecInicio", SqlDbType.DateTime).Value = reclamo.FecInicio;
            cmd.Parameters.Add("@pFlInicio", SqlDbType.Bit).Value = reclamo.FlInicio;
            cmd.Parameters.Add("@pFecFin", SqlDbType.DateTime).Value = reclamo.FecFin;
            cmd.Parameters.Add("@pFlFin", SqlDbType.Bit).Value = reclamo.FlFin;
            cmd.Parameters.Add("@pNumDNI", SqlDbType.VarChar,11).Value = reclamo.NumDNI;
            cmd.Parameters.Add("@pNombres", SqlDbType.VarChar, 1000).Value = reclamo.Nombres;
            cmd.Parameters.Add("@pNomProducto", SqlDbType.VarChar, 1000).Value = reclamo.NomProducto;
            cmd.Parameters.Add("@pDescripcion", SqlDbType.VarChar, 2000).Value = reclamo.Descripcion;
            
            try
            {
                cmd.Connection.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        be = new RecReclamo();
                        be.IdReclamo = dr.GetInt32(dr.GetOrdinal("idReclamo"));
                        be.Local.IdLocal = dr.GetInt32(dr.GetOrdinal("idLocal"));
                        be.Local.Nombre = dr.GetString(dr.GetOrdinal("local"));
                        be.NumDNI = dr.GetString(dr.GetOrdinal("numDNI"));
                        be.Nombres = dr.GetString(dr.GetOrdinal("nombres"));
                        be.FecReclamo = dr.GetDateTime(dr.GetOrdinal("fecReclamo"));
                        be.Descripcion = dr.GetString(dr.GetOrdinal("descripcion"));
                        be.Tipo = dr.GetInt32(dr.GetOrdinal("tipo"));
                        be.NomProducto = dr.GetString(dr.GetOrdinal("nomProducto"));                       
                        lista.Add(be);
                    }
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
            return lista;
        }

        public int eliminar(int codigo)
        {
            int resultado = 0;
            cmd = new SqlCommand();
            cmd.Connection = getConnection();
            cmd.CommandText = "sp_Rec_EliminarReclamo";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();

            cmd.Parameters.Add("@pIdReclamo", SqlDbType.Int, 11).Value = codigo;

            try
            {
                cmd.Connection.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        resultado = dr.GetInt32(dr.GetOrdinal("resultado"));
                    }
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
            return resultado;
        }

        public List<RecReclamo> listarSolucionados(RecReclamo reclamo)
        {
            List<RecReclamo> lista = new List<RecReclamo>();
            RecReclamo be;
            cmd = new SqlCommand();
            cmd.Connection = getConnection();
            cmd.CommandText = "sp_Rec_ListarReclamoSolucionados";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();

            cmd.Parameters.Add("@pFecInicio", SqlDbType.DateTime).Value = reclamo.FecInicio;
            cmd.Parameters.Add("@pFlInicio", SqlDbType.Bit).Value = reclamo.FlInicio;
            cmd.Parameters.Add("@pFecFin", SqlDbType.DateTime).Value = reclamo.FecFin;
            cmd.Parameters.Add("@pFlFin", SqlDbType.Bit).Value = reclamo.FlFin;
            cmd.Parameters.Add("@pNumero", SqlDbType.VarChar, 1000).Value = reclamo.IdReclamo;
            cmd.Parameters.Add("@pNombres", SqlDbType.VarChar, 1000).Value = reclamo.Nombres;
            cmd.Parameters.Add("@pNomTipoReclamo", SqlDbType.Int).Value = reclamo.Tipo;

            try
            {
                cmd.Connection.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        be = new RecReclamo();
                        be.IdReclamo = dr.GetInt32(dr.GetOrdinal("idReclamo"));
                        be.Local.IdLocal = dr.GetInt32(dr.GetOrdinal("idLocal"));
                        be.Local.Nombre = dr.GetString(dr.GetOrdinal("local"));
                        be.NumDNI = dr.GetString(dr.GetOrdinal("numDNI"));
                        be.Nombres = dr.GetString(dr.GetOrdinal("nombres"));
                        be.FecReclamo = dr.GetDateTime(dr.GetOrdinal("fecReclamo"));
                        be.Descripcion = dr.GetString(dr.GetOrdinal("descripcion"));
                        be.Tipo = dr.GetInt32(dr.GetOrdinal("tipo"));
                        be.NomProducto = dr.GetString(dr.GetOrdinal("nomProducto"));
                        lista.Add(be);
                    }
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
            return lista;
        }

        #endregion

    }
}